<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
                      "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>
    <meta http-equiv="content-type" content="text/html; charset=UTF-8"/>
    <title>Database Table Authentication - Zend Framework Manual</title>

    <link href="../css/shCore.css" rel="stylesheet" type="text/css" />
    <link href="../css/shThemeDefault.css" rel="stylesheet" type="text/css" />
    <link href="../css/styles.css" media="all" rel="stylesheet" type="text/css" />
</head>
<body>
<h1>Zend Framework</h1>
<h2>Programmer's Reference Guide</h2>
<ul>
    <li><a href="../en/zend.auth.adapter.dbtable.html">Inglês (English)</a></li>
    <li><a href="../pt-br/zend.auth.adapter.dbtable.html">Português Brasileiro (Brazilian Portuguese)</a></li>
</ul>
<table width="100%">
    <tr valign="top">
        <td width="85%">
            <table width="100%">
                <tr>
                    <td width="25%" style="text-align: left;">
                    <a href="zend.auth.introduction.html">Introduction</a>
                    </td>

                    <td width="50%" style="text-align: center;">
                        <div class="up"><span class="up"><a href="zend.auth.html">Zend_Auth</a></span><br />
                        <span class="home"><a href="manual.html">Guia de Refer&ecirc;ncia do Programador</a></span></div>
                    </td>

                    <td width="25%" style="text-align: right;">
                        <div class="next" style="text-align: right; float: right;"><a href="zend.auth.adapter.digest.html">Digest Authentication</a></div>
                    </td>
                </tr>
            </table>
<hr />
<div id="zend.auth.adapter.dbtable" class="section"><div class="info"><h1 class="title">Database Table Authentication</h1></div>
    

    <div class="section" id="zend.auth.adapter.dbtable.introduction"><div class="info"><h1 class="title">Introduction</h1></div>
        

        <p class="para">
            <span class="classname">Zend_Auth_Adapter_DbTable</span> provides the ability to
            authenticate against credentials stored in a database table. Because
            <span class="classname">Zend_Auth_Adapter_DbTable</span> requires an instance of
            <span class="classname">Zend_Db_Adapter_Abstract</span> to be passed to its
            constructor, each instance is bound to a particular database
            connection. Other configuration options may be set through the
            constructor and through instance methods, one for each option.
        </p>

        <p class="para">
            The available configuration options include:
        </p>

        <ul class="itemizedlist">
            <li class="listitem">
                <p class="para">
                    <em class="emphasis"><span class="property">tableName</span></em>: This is the name of the
                    database table that contains the authentication credentials,
                    and against which the database authentication query is
                    performed.
                </p>
            </li>

            <li class="listitem">
                <p class="para">
                    <em class="emphasis"><span class="property">identityColumn</span></em>: This is the name of
                    the database table column used to represent the identity.
                    The identity column must contain unique values, such as
                    a username or e-mail address.
                </p>
            </li>

            <li class="listitem">
                <p class="para">
                    <em class="emphasis"><span class="property">credentialColumn</span></em>: This is the name
                    of the database table column used to represent the credential.
                    Under a simple identity and password authentication
                    scheme, the credential value corresponds to the
                    password. See also the <span class="property">credentialTreatment</span>
                    option.
                </p>
            </li>

            <li class="listitem">
                <p class="para">
                    <em class="emphasis"><span class="property">credentialTreatment</span></em>: In many cases,
                    passwords and other sensitive data are encrypted,
                    hashed, encoded, obscured, salted or otherwise treated
                    through some function or algorithm. By specifying a
                    parameterized treatment string with this method, such as
                    &#039; <span class="methodname">MD5(?)</span>&#039; or
                    &#039; <span class="methodname">PASSWORD(?)</span>&#039;, a
                    developer may apply such arbitrary <acronym class="acronym">SQL</acronym> upon input
                    credential data. Since these functions are specific to
                    the underlying <acronym class="acronym">RDBMS</acronym>, check the database manual for the
                    availability of such functions for your database system.
                </p>
            </li>
        </ul>

        <div class="example" id="zend.auth.adapter.dbtable.introduction.example.basic_usage"><div class="info"><p><b>Example #1 Basic Usage</b></p></div>
            

            <div class="example-contents"><p>
                As explained in the introduction, the
                <span class="classname">Zend_Auth_Adapter_DbTable</span> constructor requires an
                instance of <span class="classname">Zend_Db_Adapter_Abstract</span> that serves as
                the database connection to which the authentication adapter
                instance is bound. First, the database connection should be
                created.
            </p></div>

            <div class="example-contents"><p>
                The following code creates an adapter for an in-memory database,
                creates a simple table schema, and inserts a row against
                which we can perform an authentication query later. This example
                requires the <acronym class="acronym">PDO</acronym> SQLite extension to be available:
            </p></div>

            <pre class="programlisting brush: php">
// Create an in-memory SQLite database connection
$dbAdapter = new Zend_Db_Adapter_Pdo_Sqlite(array(&#039;dbname&#039; =&gt;
                                                  &#039;:memory:&#039;));

// Build a simple table creation query
$sqlCreate = &#039;CREATE TABLE [users] (&#039;
           . &#039;[id] INTEGER  NOT NULL PRIMARY KEY, &#039;
           . &#039;[username] VARCHAR(50) UNIQUE NOT NULL, &#039;
           . &#039;[password] VARCHAR(32) NULL, &#039;
           . &#039;[real_name] VARCHAR(150) NULL)&#039;;

// Create the authentication credentials table
$dbAdapter-&gt;query($sqlCreate);

// Build a query to insert a row for which authentication may succeed
$sqlInsert = &quot;INSERT INTO users (username, password, real_name) &quot;
           . &quot;VALUES (&#039;my_username&#039;, &#039;my_password&#039;, &#039;My Real Name&#039;)&quot;;

// Insert the data
$dbAdapter-&gt;query($sqlInsert);
</pre>


            <div class="example-contents"><p>
                With the database connection and table data available, an
                instance of <span class="classname">Zend_Auth_Adapter_DbTable</span> may be
                created. Configuration option values may be passed to the
                constructor or deferred as parameters to setter methods after
                instantiation:
            </p></div>

            <pre class="programlisting brush: php">
// Configure the instance with constructor parameters...
$authAdapter = new Zend_Auth_Adapter_DbTable(
    $dbAdapter,
    &#039;users&#039;,
    &#039;username&#039;,
    &#039;password&#039;
);

// ...or configure the instance with setter methods
$authAdapter = new Zend_Auth_Adapter_DbTable($dbAdapter);

$authAdapter
    -&gt;setTableName(&#039;users&#039;)
    -&gt;setIdentityColumn(&#039;username&#039;)
    -&gt;setCredentialColumn(&#039;password&#039;)
;
</pre>


            <div class="example-contents"><p>
                At this point, the authentication adapter instance is ready to
                accept authentication queries. In order to formulate an
                authentication query, the input credential values are passed to
                the adapter prior to calling the  <span class="methodname">authenticate()</span>
                method:
            </p></div>

            <pre class="programlisting brush: php">
// Set the input credential values (e.g., from a login form)
$authAdapter
    -&gt;setIdentity(&#039;my_username&#039;)
    -&gt;setCredential(&#039;my_password&#039;)
;

// Perform the authentication query, saving the result
</pre>


            <div class="example-contents"><p>
                In addition to the availability of the
                 <span class="methodname">getIdentity()</span> method upon the authentication result
                object, <span class="classname">Zend_Auth_Adapter_DbTable</span> also supports
                retrieving the table row upon authentication success:
            </p></div>

            <pre class="programlisting brush: php">
// Print the identity
echo $result-&gt;getIdentity() . &quot;\n\n&quot;;

// Print the result row
print_r($authAdapter-&gt;getResultRowObject());

/* Output:
my_username

Array
(
    [id] =&gt; 1
    [username] =&gt; my_username
    [password] =&gt; my_password
    [real_name] =&gt; My Real Name
)
</pre>


            <div class="example-contents"><p>
                Since the table row contains the credential value, it is
                important to secure the values against unintended access.
            </p></div>
        </div>
    </div>

    <div class="section" id="zend.auth.adapter.dbtable.advanced.storing_result_row"><div class="info"><h1 class="title">Advanced Usage: Persisting a DbTable Result Object</h1></div>
        

        <p class="para">
            By default, <span class="classname">Zend_Auth_Adapter_DbTable</span> returns the
            identity supplied back to the auth object upon successful
            authentication. Another use case scenario, where developers want to
            store to the persistent storage mechanism of <span class="classname">Zend_Auth</span>
            an identity object containing other useful information, is solved by
             using the  <span class="methodname">getResultRowObject()</span> method to return a
            <em class="emphasis">stdClass</em> object. The following code snippet illustrates
            its use:
        </p>

        <pre class="programlisting brush: php">
// authenticate with Zend_Auth_Adapter_DbTable
$result = $this-&gt;_auth-&gt;authenticate($adapter);

if ($result-&gt;isValid()) {
    // store the identity as an object where only the username and
    // real_name have been returned
    $storage = $this-&gt;_auth-&gt;getStorage();
    $storage-&gt;write($adapter-&gt;getResultRowObject(array(
        &#039;username&#039;,
        &#039;real_name&#039;,
    )));

    // store the identity as an object where the password column has
    // been omitted
    $storage-&gt;write($adapter-&gt;getResultRowObject(
        null,
        &#039;password&#039;
    ));

    /* ... */

} else {

    /* ... */

}
</pre>

    </div>

    <div class="section" id="zend.auth.adapter.dbtable.advanced.advanced_usage"><div class="info"><h1 class="title">Advanced Usage By Example</h1></div>
        

        <p class="para">
            While the primary purpose of <span class="classname">Zend_Auth</span> (and consequently
            <span class="classname">Zend_Auth_Adapter_DbTable</span>) is primarily
            <em class="emphasis">authentication</em> and not
            <em class="emphasis">authorization</em>, there are a few
            instances and problems that toe the line between which domain they fit
            within. Depending on how you&#039;ve decided to explain your problem, it
             sometimes makes sense to solve what could look like an
             authorization problem within the authentication adapter.
        </p>

        <p class="para">
            With that disclaimer out of the way, <span class="classname">Zend_Auth_Adapter_DbTable</span>
            has some built in mechanisms that can be leveraged for additional checks at
            authentication time to solve some common user problems.
        </p>

        <pre class="programlisting brush: php">
// The status field value of an account is not equal to &quot;compromised&quot;
$adapter = new Zend_Auth_Adapter_DbTable(
    $db,
    &#039;users&#039;,
    &#039;username&#039;,
    &#039;password&#039;,
    &#039;MD5(?) AND status != &quot;compromised&quot;&#039;
);

// The active field value of an account is equal to &quot;TRUE&quot;
$adapter = new Zend_Auth_Adapter_DbTable(
    $db,
    &#039;users&#039;,
    &#039;username&#039;,
    &#039;password&#039;,
    &#039;MD5(?) AND active = &quot;TRUE&quot;&#039;
</pre>


        <p class="para">
            Another scenario can be the implementation of a salting mechanism.
            Salting is a term referring to a technique which can highly improve
            your application&#039;s security. It&#039;s based on the idea that
            concatenating a random string to every password makes it impossible
            to accomplish a successful brute force attack on the database using
            pre-computed hash values from a dictionary.
        </p>

        <p class="para">
            Therefore, we need to modify our table to store our salt string:
        </p>

        <pre class="programlisting brush: php">
$sqlAlter = &quot;ALTER TABLE [users] &quot;
          . &quot;ADD COLUMN [password_salt] &quot;
          . &quot;AFTER [password]&quot;;
</pre>


        <p class="para">
            Here&#039;s a simple way to generate a salt string for every user at
            registration:
        </p>

        <pre class="programlisting brush: php">
for ($i = 0; $i &lt; 50; $i++) {
    $dynamicSalt .= chr(rand(33, 126));
</pre>

        <p class="para">
            And now let&#039;s build the adapter:
        </p>

        <pre class="programlisting brush: php">
$adapter = new Zend_Auth_Adapter_DbTable(
    $db,
    &#039;users&#039;,
    &#039;username&#039;,
    &#039;password&#039;,
    &quot;MD5(CONCAT(&#039;&quot;
    . Zend_Registry::get(&#039;staticSalt&#039;)
    . &quot;&#039;, ?, password_salt))&quot;
);
</pre>


        <blockquote class="note"><p><b class="note">Note</b>: 
            <p class="para">
                You can improve security even more by using a static salt value
                hard coded into your application. In the case that your database
                is compromised (e. g. by an <acronym class="acronym">SQL</acronym> injection attack) but your web
                server is intact your data is still unusable for the attacker.
            </p>
        </p></blockquote>

        <p class="para">
            Another alternative is to use the  <span class="methodname">getDbSelect()</span> method
            of the <span class="classname">Zend_Auth_Adapter_DbTable</span> after the adapter has been
            constructed. This method will return the <span class="classname">Zend_Db_Select</span> object
            instance it will use to complete the  <span class="methodname">authenticate()</span> routine.
            It is important to note that this method will always return the same object regardless
            if  <span class="methodname">authenticate()</span> has been called or not. This object
            <em class="emphasis">will not</em> have any of the identity or credential information in it
            as those values are placed into the select object at
             <span class="methodname">authenticate()</span> time.
        </p>

        <p class="para">
            An example of a situation where one might want to use the
             <span class="methodname">getDbSelect()</span> method would check the status of a user, in
            other words to see if that user&#039;s account is enabled.
        </p>

        <pre class="programlisting brush: php">
// Continuing with the example from above
$adapter = new Zend_Auth_Adapter_DbTable(
    $db,
    &#039;users&#039;,
    &#039;username&#039;,
    &#039;password&#039;,
    &#039;MD5(?)&#039;
);

// get select object (by reference)
$select = $adapter-&gt;getDbSelect();
$select-&gt;where(&#039;active = &quot;TRUE&quot;&#039;);

// authenticate, this ensures that users.active = TRUE
$adapter-&gt;authenticate();
</pre>

    </div>
</div>
        <hr />

            <table width="100%">
                <tr>
                    <td width="25%" style="text-align: left;">
                    <a href="zend.auth.introduction.html">Introduction</a>
                    </td>

                    <td width="50%" style="text-align: center;">
                        <div class="up"><span class="up"><a href="zend.auth.html">Zend_Auth</a></span><br />
                        <span class="home"><a href="manual.html">Guia de Refer&ecirc;ncia do Programador</a></span></div>
                    </td>

                    <td width="25%" style="text-align: right;">
                        <div class="next" style="text-align: right; float: right;"><a href="zend.auth.adapter.digest.html">Digest Authentication</a></div>
                    </td>
                </tr>
            </table>
</td>
        <td style="font-size: smaller;" width="15%"> <style type="text/css">
#leftbar {
	float: left;
	width: 186px;
	padding: 5px;
	font-size: smaller;
}
ul.toc {
	margin: 0px 5px 5px 5px;
	padding: 0px;
}
ul.toc li {
	font-size: 85%;
	margin: 1px 0 1px 1px;
	padding: 1px 0 1px 11px;
	list-style-type: none;
	background-repeat: no-repeat;
	background-position: center left;
}
ul.toc li.header {
	font-size: 115%;
	padding: 5px 0px 5px 11px;
	border-bottom: 1px solid #cccccc;
	margin-bottom: 5px;
}
ul.toc li.active {
	font-weight: bold;
}
ul.toc li a {
	text-decoration: none;
}
ul.toc li a:hover {
	text-decoration: underline;
}
</style>
 <ul class="toc">
  <li class="header home"><a href="manual.html">Guia de Refer&ecirc;ncia do Programador</a></li>
  <li class="header up"><a href="manual.html">Guia de Refer&ecirc;ncia do Programador</a></li>
  <li class="header up"><a href="reference.html">Refer&ecirc;ncia do Zend Framework</a></li>
  <li class="header up"><a href="zend.auth.html">Zend_Auth</a></li>
  <li><a href="zend.auth.introduction.html">Introduction</a></li>
  <li class="active"><a href="zend.auth.adapter.dbtable.html">Database Table Authentication</a></li>
  <li><a href="zend.auth.adapter.digest.html">Digest Authentication</a></li>
  <li><a href="zend.auth.adapter.http.html">HTTP Authentication Adapter</a></li>
  <li><a href="zend.auth.adapter.ldap.html">LDAP Authentication</a></li>
  <li><a href="zend.auth.adapter.openid.html">Open ID Authentication</a></li>
 </ul>
 </td>
    </tr>
</table>

<script type="text/javascript" src="../js/shCore.js"></script>
<script type="text/javascript" src="../js/shAutoloader.js"></script>
<script type="text/javascript" src="../js/main.js"></script>

</body>
</html>